#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date    : 2014-08-22 01:14:23
# @Author  : shitao.tommy (hero007asd@gmail.com)
# @Link    : http://example.org
# @Version : $Id$

from framework.db.basedb import BaseDb
from tornado import gen
from lib import log,libtime,libpage,libhttp,libjson
from lib.app import libqrcode

class Qr(BaseDb):

    @gen.coroutine
    def new_batch(self, num=1):
        '''批量生成二维码'''
        resp = ''
        ins_sql = '''INSERT INTO qr_qrcode(F1_qrcode,F2_qrcode,Fcreate_time) VALUES '''
        param_list = []
        qr_arr = libqrcode.generate_qr(num)
        ins_arr = []
        ins_arr.append(ins_sql)
        for i in qr_arr:
            # ins_sql = ins_sql + '(%s,%s,UNIX_TIMESTAMP()),'
            ins_arr.append('(%s,%s,UNIX_TIMESTAMP()),')
            param_list.append(i[0])
            param_list.append(i[1])
        ins_str = ''.join(ins_arr)
        try:
            ret = yield self.upd_or_ins(ins_str[:-1],tuple(param_list))
            if ret > 0: resp = '1'
            else: resp = '0' 
        except Exception, e:
            import traceback
            e = traceback.format_exc()
            log.log_exception(self.__class__.__name__, e)
            resp = '-1'
        raise gen.Return(resp)

    @gen.coroutine
    def num_batch(self, token):
        '''绑定数量信息'''
        resp = ''
        sel_bind_sql = '''SELECT COUNT(1)
                    FROM qr_qrcode
                    WHERE Ftoken = %s
                    AND Fout_id IS NULL'''
        sel_not_bind_sql = '''SELECT COUNT(1)
                    FROM qr_qrcode
                    WHERE Ftoken = %s
                    AND Fout_id IS NOT NULL'''

        try:
            bind_ret = yield self.execute(sel_bind_sql,(token,))
            not_bind_ret = yield self.execute(sel_not_bind_sql,(token,))
            resp = {}
            resp ={'restNum':not_bind_ret[0][0], 'bandNum':bind_ret[0][0]}
        except Exception, e:
            import traceback
            e = traceback.format_exc()
            log.log_exception(self.__class__.__name__, e)
            resp = '-1'
        raise gen.Return(resp)

    @gen.coroutine
    def bind_batch(self, token, out_id, num=1):
        '''批量绑定二维码'''
        resp = ''
        upd_sql = '''UPDATE qr_qrcode
                    SET Fout_id = %s
                    ,Fbindtime = UNIX_TIMESTAMP()
                    WHERE Ftoken = %s
                    AND Fout_id IS NULL
                    ORDER BY Fid ASC LIMIT %s'''

        try:
            try:
                num = int(num)
            except Exception, e:
                num = 0
            ret = yield self.upd_or_ins(upd_sql,(out_id, token, num))
            if ret > 0: resp = '1'
            else: resp = '0'
        except Exception, e:
            import traceback
            e = traceback.format_exc()
            log.log_exception(self.__class__.__name__, e)
            resp = '-1'
        raise gen.Return(resp)


    @gen.coroutine
    def bind_token(self, token, num=1):
        '''绑定商户token'''
        resp = ''
        upd_sql = '''UPDATE qr_qrcode
                    SET Ftoken = %s
                    WHERE Fout_id IS NULL
                    ORDER BY Fid ASC LIMIT %s'''
        try:
            ret = yield self.upd_or_ins(upd_sql,(token, num))
            if ret > 0: resp = '1'
            else: resp = '0'
        except Exception, e:
            import traceback
            e = traceback.format_exc()
            log.log_exception(self.__class__.__name__, e)
            resp = '-1'
        raise gen.Return(resp)



    @gen.coroutine
    def list_bond(self, token, out_id, page, offset):
        '''已绑定二维码信息'''
        resp = ''
        sel_sql = '''SELECT Fid,F1_qrcode,F2_qrcode,Fis_ban,Fcreate_time,Fscan1_count,Fscan1_time
                        ,Fscan2_count,Fscan2_time,Fbindtime,Ftoken,Fis_lock
                    FROM qr_qrcode
                    WHERE Ftoken = %s
                    AND Fout_id = %s
                    ORDER BY Fid DESC
                    LIMIT %s,%s'''
        try:
            ret = yield self.execute(sel_sql,(token, out_id, libpage.start(page, offset), offset))
            resp = []
            for i in ret:
                resp.append({'id':i[0],'fristQrcode':i[1],'secondQrcode':i[2],'isBan':i[3],
                    'createTime':i[4],'scan1Count':i[5],'scan1Time':i[6],'scan2Count':i[7],
                    'scan2Time':i[8],'bindtime':i[9],'token':i[10],'isLock':i[11]})

        except Exception, e:
            import traceback
            e = traceback.format_exc()
            log.log_exception(self.__class__.__name__, e)
            resp = '-1'
        raise gen.Return(resp)



    @gen.coroutine
    def qrcode_dtl(self, qr_code, ip):
        '''二维码基本信息'''
        resp = ''
        sel_sql = '''SELECT a.Fout_id, a.F1_qrcode, b.Fbrand, b.Fpart_no,b.Fnum, b.Fweight, b.Fdata_code, b.Fship_time
                    FROM qr_qrcode a
                    LEFT JOIN qr_out_storage b
                    ON a.Fout_id = b.Fid
                    WHERE a.F1_qrcode = %s'''
        ins_sql = '''INSERT INTO qr_query_log(Fqrcode,Fip_address,Fcity,Fclient_name,Ftoken,Fcreate_time)
                    SELECT %s,%s,%s,b.Fclient,b.Ftoken,UNIX_TIMESTAMP()
                    FROM qr_qrcode a
                    LEFT JOIN qr_out_storage b
                    ON a.Fout_id = b.Fid
                    WHERE a.F1_qrcode = %s'''
        try:
            ip_info = libhttp.get_url_body('http://int.dpool.sina.com.cn/iplookup/iplookup.php?format=json&ip='+ip)
            ret = yield self.execute(sel_sql,(qr_code,))
            ip_city = libjson.to_decode_json(ip_info)['city']
            ins_ret = yield self.upd_or_ins(ins_sql,(qr_code,ip,ip_city,qr_code))
            resp = {}
            for i in ret:
                resp = {'outId':i[0],'fristQrcode':i[1],'brand':i[2],'partNo':i[3],
                    'num':i[4],'weight':i[5],'dataCode':i[6],'shipTime':i[7]}
        except Exception, e:
            import traceback
            e = traceback.format_exc()
            log.log_exception(self.__class__.__name__, e)
            resp = '-1'
        raise gen.Return(resp)


    @gen.coroutine
    def qrcode_secret_dtl(self, qr_code, user, pwd):
        '''二维码私密信息'''
        resp = ''
        sel_sql = '''SELECT b.Fclient, b.Fclient_pn,b.Forder_no
                    FROM qr_qrcode a
                    LEFT JOIN qr_out_storage b
                    ON a.Fout_id = b.Fid
                    WHERE a.F1_qrcode = %s
                    AND b.Fuser = %s
                    AND b.Fpwd = %s'''
        try:
            ret = yield self.execute(sel_sql,(qr_code, user, pwd))
            resp = {}
            for i in ret:
                resp = {'client':i[0],'clientPn':i[1],'orderNo':i[2]}
        except Exception, e:
            import traceback
            e = traceback.format_exc()
            log.log_exception(self.__class__.__name__, e)
            resp = '-1'
        raise gen.Return(resp)


    @gen.coroutine
    def query_log(self, token, page, offset, qrcode='', city='', deliver_address=''):
        '''查询日志'''
        resp = ''
        sel_sql = '''SELECT a.Fqrcode,a.Fip_address,a.Fcity,a.Fclient_name,a.Fcreate_time,c.Fdeliver_address FROM qr_query_log a
                    LEFT JOIN qr_qrcode b
                    ON a.Fqrcode = b.F1_qrcode
                    LEFT JOIN qr_out_storage c
                    ON b.Fout_id = c.Fid
                    WHERE a.Ftoken = %s '''
        try:
            stmpsql = []
            stmpsql.append(sel_sql)
            param_list = [token]
            if qrcode!='':
                stmpsql.append(' AND a.Fqrcode = %s')
                param_list.append(qrcode)
            if city!='':
                stmpsql.append(' AND a.Fcity = %s')
                param_list.append(city)
            if deliver_address!='':
                stmpsql.append(' AND c.Fdeliver_address = %s')
                param_list.append(deliver_address)
            stmpsql.append(' ORDER BY a.Fid DESC LIMIT %s,%s')
            param_list.append(libpage.start(page, offset))
            param_list.append(offset)
            sel_sql = ''.join(stmpsql)

            ret = yield self.execute(sel_sql,tuple(param_list))
            resp = []
            for i in ret:
                resp.append({'qrcode':i[0],'ipaddress':i[1],'city':i[2],'clientName':i[3],
                    'createTime':i[4],'outAddress':i[5]})

        except Exception, e:
            import traceback
            e = traceback.format_exc()
            log.log_exception(self.__class__.__name__, e)
            resp = '-1'
        raise gen.Return(resp)

    @gen.coroutine
    def query_loginfo(self, token, qrcode):
        '''查询日志详细信息'''
        resp = ''
        sel_sql = '''SELECT b.Fbrand, b.Fpart_no, b.Fnum, b.Fweight, b.Fdata_code
                    , b.Fship_time, b.Fdeliver_address, b.Fclient, b.Fclient_pn
                    , b.Forder_no, b.Fuser, b.Fpwd
                    FROM qr_qrcode a 
                    RIGHT JOIN qr_out_storage b 
                    ON b.Fid = a.Fout_id 
                    AND a.F1_qrcode = %s
                    WHERE a.Ftoken = %s '''
        try:

            ret = yield self.execute(sel_sql, (qrcode, token))
            resp = []
            for i in ret:
                resp.append({'brand':i[0],'partNo':i[1],'num':i[2],'weight':i[3],
                    'dataCode':i[4],'shipTime':i[5],'deliverAddress':i[6],'client':i[7]
                    ,'clientPn':i[8],'orderNo':i[9],'user':i[10],'pwd':i[11]})
        except Exception, e:
            import traceback
            e = traceback.format_exc()
            log.log_exception(self.__class__.__name__, e)
            resp = '-1'
        raise gen.Return(resp)